package com.example.javaeefinal.Mapper;

import com.example.javaeefinal.pojo.User;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface UserMapper {
    //查找密码
    @Select("select F_id as id, F_username as username, F_logname as logName, F_Position as position, F_password as password  from t_user where F_logname = #{username}")
    User findPasswordByUserName(String username);

    @Insert("insert into t_user(F_username, F_password)" +
            "values (#{username}, #{password})")
    void add(String username, String password);

    //查找实体信息
    @Select("SELECT t_user.F_id AS id,\n" +
            "       t_user.F_username AS username,\n" +
            "       t_user.F_password AS password,\n" +
            "       t_user.F_logname AS logname,\n" +
            "       t_position.F_name AS position,\n" +
            "       t_userinfo.F_number AS phone,\n" +
            "       t_userinfo.F_mail AS mail,\n" +
            "       t_userinfo.`F_describe` AS `describe`\n" +
            "FROM t_user\n" +
            "         INNER JOIN t_position ON t_user.F_Position = t_position.F_id\n" +
            "         LEFT JOIN t_userinfo ON t_user.F_id = t_userinfo.F_id\n" +
            "WHERE t_user.F_id = #{id}; ")
    User findUserById(int id);

    @Update("UPDATE t_user " +
            "SET F_username = #{username}, " +
            "F_password = #{password}, " +
            "F_logname = #{logname}, " +
            "F_Position = #{position}, " +
            "F_role = #{role} " +
            "WHERE F_id = #{id}")
    void updateUserById(User user);

    @Update("UPDATE t_userInfo " +
            "SET F_number = #{number}, " +
            "F_mail = #{mail}, " +
            "F_describe = #{describe} " +
            "WHERE F_id = #{id}")
    void updateUserInfoById(User user);

    //根据用户名模糊查找
    @Select("SELECT\n" +
            "    t_user.F_id AS id,\n" +
            "    t_user.F_username AS username,\n" +
            "    t_user.F_password AS password,\n" +
            "    t_user.F_logname AS logname,\n" +
            "    t_position.F_name AS position,\n" +
            "    t_role.f_name AS role,\n" +
            "    t_userinfo.F_number AS number,\n" +
            "    t_userinfo.F_mail AS mail,\n" +
            "    t_userinfo.F_describe AS `describe`\n" +
            "FROM\n" +
            "    t_user\n" +
            "    LEFT JOIN\n" +
            "    t_position ON t_user.F_Position = t_position.F_id\n" +
            "    LEFT JOIN\n" +
            "    t_userinfo ON t_user.F_id = t_userinfo.F_id\n" +
            "    LEFT JOIN\n" +
            "    t_user_role_relationship ON t_user.F_id = t_user_role_relationship.F_user_id\n" +
            "    LEFT JOIN\n" +
            "    t_role ON t_user_role_relationship.F_role_id = t_role.f_id\n" +
            "WHERE\n" +
            "    t_user.F_username LIKE '%${value}%'\n" +
            "LIMIT #{pageSize} OFFSET #{rowsNumber};\n")
    List<User> findUserListByUsername(String value,int pageSize,int rowsNumber);
    //根据手机号模糊查找
    @Select("SELECT\n" +
            "    t_user.F_id AS id,\n" +
            "    t_user.F_username AS username,\n" +
            "    t_user.F_password AS password,\n" +
            "    t_user.F_logname AS logname,\n" +
            "    t_position.F_name AS position,\n" +
            "    t_role.f_name AS role,\n" +
            "    t_userinfo.F_number AS number,\n" +
            "    t_userinfo.F_mail AS mail,\n" +

            "    t_userinfo.F_describe AS `describe`\n" +
            "FROM\n" +
            "    t_user\n" +
            "    LEFT JOIN t_position ON t_user.F_Position = t_position.F_id\n" +
            "    LEFT JOIN t_userinfo ON t_user.F_id = t_userinfo.F_id\n" +
            "    LEFT JOIN t_user_role_relationship ON t_user.F_id = t_user_role_relationship.F_user_id\n" +
            "    LEFT JOIN t_role ON t_user_role_relationship.F_role_id = t_role.f_id\n" +
            "WHERE\n" +
            "    t_userinfo.F_number LIKE '%${value}%'\n" +
            "LIMIT #{pageSize} OFFSET #{rowsNumber};\n")
    List<User> findUserListByPhoneNumber(String value,int pageSize,int rowsNumber);

    @Insert("insert into t_user_role_relationship(F_user_id, F_role_id)\n" +
    "values (#{userId}, #{roleId})")
    void addRole(int userId, int roleId);

    @Select("select F_username as username from t_user where F_id = #{userId}")
    User isExistByUid(int userId);

    @Select("SELECT t_user.F_id AS id,\n" +
            "       t_user.F_username AS username,\n" +
            "       t_user.F_logname AS logName,\n" +
            "       t_position.F_name AS position,\n" +
            "       t_clientinfo.F_phone AS phone,\n" +
            "       t_clientinfo.F_email AS mail,\n" +
            "       t_userinfo.F_describe AS `describe`,\n" +
            "       t_user.F_password AS password\n" +
            "FROM t_user\n" +
            "    JOIN t_user_role_relationship ON t_user.F_id = t_user_role_relationship.F_user_id\n" +
            "    JOIN t_position ON t_user.F_Position = t_position.F_id\n" +
            "    LEFT JOIN t_clientinfo ON t_user.F_id = t_clientinfo.F_userId\n" +
            "    LEFT JOIN t_userinfo ON t_user.F_id = t_userinfo.F_id\n" +
            "WHERE t_user_role_relationship.F_role_id = #{roleId};")
    List<User> findUsersRid(int roleId);

    @Delete("delete from t_user_role_relationship where F_user_id = #{userId} and F_role_id = #{roleId}")
    void delRole(int userId, int roleId);


    //插入新用户
    void insertNewUser(User user);

    //插入新用户信息
    void insertNewUserInfo(User user);

    @Select("SELECT COUNT(*) AS total_users\n" +
            "FROM t_user;\n")
    int countUser();
}
